Oracle 数据库性能排查 SQL
2026-06-04
1445 字
3 分钟
0
阅读
Oracle 性能排查与锁表处理常用 SQL
一、查看会话内存占用
查看哪个会话在占用物理内存(PGA)
Select Server, Osuser, Name, Value / 1024 / 1024 Mb, s.Sql_Id, Spid, s.*
From V$session s, V$sesstat St, V$statname Sn, V$process p
Where St.Sid = s.Sid
And St.Statistic# = Sn.Statistic#
And Sn.Name Like 'session pga memory'
And p.Addr = s.Paddr
Order By Value Desc;
二、查询最占用资源的 SQL
查询高磁盘读取的 SQL(消耗资源TOP)
select b.username username,
a.disk_reads reads,
a.executions exec ,
a.disk_reads / decode(a.executions, 0, 1, a.executions) rds_exec_ratio,
a.sql_text Statement
from v$sqlarea a, dba_users b
where a.parsing_user_id = b.user_id
and a.disk_reads > 100000
order by a.disk_reads desc;
使用频率最高的 5 条 SQL
select sql_text, executions
from (select sql_text,
executions,
rank() over(order by executions desc) exec_rank
from v$sql)
where exec_rank <= 5;
磁盘读取最多 TOP5 SQL
select disk_reads, sql_text
from (select sql_text,
disk_reads,
dense_rank() over(order by disk_reads desc) disk_reads_rank
from v$sql)
where disk_reads_rank <= 5;
逻辑读(内存消耗)最多 SQL
select buffer_gets, sql_text
from (select sql_text,
buffer_gets,
dense_rank() over(order by buffer_gets desc) buffer_gets_rank
from v$sql)
where buffer_gets_rank <= 5;
三、查看当前活动会话
SELECT status, a.sql_exec_start AS "开始时间", SYSDATE AS "当前时间",
round((SYSDATE - a.sql_exec_start) * 24 * 60) AS "执行时长min", b.sql_id,
b.sql_text, b.sql_fulltext, a.sid, a.serial#, a.blocking_session,
a.username, a.machine, a.event,
'alter system kill session ''' || a.sid || ',' || a.serial# || ''';' AS killstment
FROM v$session a, v$sqlarea b
WHERE a.sql_id = b.sql_id
AND status = 'ACTIVE'
ORDER BY 4 DESC;